The following U.S. patents and publications provide useful background information, for which they are incorporated herein by reference in their entirety.
7,085,780August 2006Sakamoto, et al.7,082,455July 2006Hu, et al.6,769,074July 2004Vaitzblit6,507,834January 2003Kabra, et al.20060136493June 2006Muralidharan, et al.20040162822August 2004Papanyan, et al.20040162825August 2004Bhaghavan, et al.
A stored procedure is a named group of SQL statements previously created and stored in a database server. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. Additionally, stored procedures can be used to ensure the integrity of transactions. For example, the following stored procedure allows a user to get the inventory levels for a given warehouse:
CREATE PROCEDURE sp_GetInventory @location varchar(10) AS SELECT Product, Quantity FROM Inventory WHERE Warehouse=@location.
The stored procedure named “sp_GetInventory” includes the SQL statement:
“SELECT Product, Quantity FROM Inventory WHERE Warehouse=@location”
where the location is an input parameter. The database table accessed by this procedure is “Inventory” and the operation performed on this table is “SELECT”. A user can retrieve inventory levels by providing the desired warehouse's location as an input to the procedure and issuing a command:
EXECUTE sp_GetInventory ‘New-York’
For security of data and transaction integrity purposes it is desirable to monitor access to underlying tables in stored procedures and enforce access control on such tables. Related art techniques for monitoring tables that are accessed through stored procedures are based on a database internal audit trail mechanism. This introduces a major drawback as an attacker may take over the entire database or simply turn off the audit mechanism, and thus removing any traces of his activity. Another drawback is that such techniques generate many redundant records and events that need to be processed. For example, a stored procedure may include tens of operations to various tables. A single invocation of the stored procedure creates many access records that must be processed. As a result, the overall performance of a database system is reduced. Another drawback of related art techniques is the miscorrelation between procedure invocation and table access operations, i.e., a user (e.g., a system administrator) cannot correlate a suspicious access to a table to the invocation of a stored procedure. Thus, the user cannot detect the cause for an illegal access and take corrective actions. In addition, the audit based monitoring techniques cannot distinguish between an access made through a stored procedure, considered as an authorized access, and direct access to a table (e.g., by submitting a SQL statement) which is an unauthorized access.
In view of the shortcomings of related art techniques it would be advantageous to provide an efficient, effective and secure solution for monitoring stored procedures.